#!/bin/bash

#0 2 * * * backup_online4mysql56.sh > /tmp/backup.log 2>&1

##############################################################
#def vars

DBUSR='xxx'
DBPWD='xxx'
DBSOCK='/var/lib/mysql/mysql.sock'
DBPORT=3306
TABFILE='/tab.lst'
MYCFGFILE='/etc/my.cnf'
BACKUPDIR='/backup'

##############################################################
#generate table list

mysql -s -u${DBUSR} -p${DBPWD} --socket=${DBSOCK} --port=${DBPORT} \
> ${TABFILE} \
<<EOF
select
    concat(table_schema,'.',table_name) tablist
from
    information_schema.tables t
where
     NOT( (table_schema in('db1') and table_name like 'tab1%') or
      (table_schema in('db2') and table_name like 'tab2%') or
      (table_schema in('db3') and (table_name in('tab4','tab5','tab6'))) or
      (table_schema in('db4')));
EOF

##############################################################
#execute backup

mysqldump -d --all-databases -u${DBUSR} -p${DBPWD} --socket=${DBSOCK} --port=${DBPORT} \
> ${BACKUPDIR}/backup_structure.$(date "+%s") \
2> ${BACKUPDIR}/backup_structure_log.$(date "+%s")

innobackupex --defaults-file=${MYCFGFILE} --user=${DBUSR} --password=${DBPWD} --socket=${DBSOCK} \
--tables-file=${TABFILE} ${BACKUPDIR} > ${BACKUPDIR}/backup_log.$(date "+%s") 2>&1

cp ${MYCFGFILE} ${BACKUPDIR}/my.cnf.$(date "+%s")

##############################################################
#delete old backup pieces

find /xtrabackup/20* -mtime +1 -name "*" -exec rm -rf {} \;